﻿-- =============================================
-- Script Template
-- =============================================
use PDT
Go

DECLARE @table_schema varchar(100)
       ,@table_name varchar(100)
       ,@constraint_schema varchar(100)
       ,@constraint_name varchar(100)
       ,@cmd nvarchar(200)


--
-- drop all the constraints
--
DECLARE constraint_cursor CURSOR FOR
  select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   where TABLE_NAME != 'sysdiagrams'
    order by CONSTRAINT_TYPE asc -- FOREIGN KEY, then PRIMARY KEY
     

OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @constraint_schema, @constraint_name, @table_schema, @table_name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @cmd = 'ALTER TABLE ' + @table_schema + '.' + @table_name + ' DROP CONSTRAINT ' + @constraint_name
     EXEC sp_executesql @cmd

     FETCH NEXT FROM constraint_cursor INTO @constraint_schema, @constraint_name, @table_schema, @table_name
END

CLOSE constraint_cursor
DEALLOCATE constraint_cursor

DECLARE table_cursor CURSOR FOR
  select TABLE_SCHEMA, TABLE_NAME
    from INFORMATION_SCHEMA.TABLES
   where TABLE_NAME != 'sysdiagrams'

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_schema, @table_name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @cmd = 'DROP TABLE ' + @table_schema + '.' + @table_name
     EXEC sp_executesql @cmd
     FETCH NEXT FROM table_cursor INTO @table_schema, @table_name
END

CLOSE table_cursor 
DEALLOCATE table_cursor

--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ModuleParameter') DROP TABLE ModuleParameter;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Module_Channel') DROP TABLE Module_Channel;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ModuleType_ChannelType') DROP TABLE ModuleType_ChannelType;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Channel') DROP TABLE Channel;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Module') DROP TABLE Module;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ChannelAddress') DROP TABLE ChannelAddress;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ChannelType') DROP TABLE ChannelType;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ModuleType') DROP TABLE ModuleType;
--IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Base_Ch') DROP TABLE Base_Ch;

CREATE TABLE [dbo].[Base_Ch](
	[Base_Ch_Id] [int] IDENTITY(1,1) NOT NULL,
	[Channel] [int] NULL,
	[Base_Ch] [int] NULL,
	[Neg] [bit] NOT NULL,
 CONSTRAINT [PK_Base_Ch] PRIMARY KEY CLUSTERED 
(
	[Base_Ch_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ChannelType](
	[ChannelType_Id] [int] NOT NULL,
	[BaseType] [int] NULL,
	[Description] [nvarchar](80) NOT NULL,
	[Unit] [nvarchar](8) NULL,
CONSTRAINT [ChannelType_PK] PRIMARY KEY CLUSTERED 
(
	[ChannelType_Id] ASC
) ON [PRIMARY],
CONSTRAINT [ChannelType_Description_U] UNIQUE NONCLUSTERED 
(
	[Description]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ChannelAddress](
	[ChannelAddress_Id] [int] IDENTITY(1,1) NOT NULL,
	[Port] [int] NULL,
	[Drive] [int] NULL,
	[Unit] [int] NULL,
	[Subunit] [int] NULL,
	[AddressByte] [int] NULL,
	[AddressBit] [int] NULL,
	[Inverted] [bit] NOT NULL,
	[Hi] [int] NULL,
	[Low] [int] NULL,
	[WordLength] [int] NULL,
	[On_Error] [int] NULL,
	[AsyncType] [bit] NULL,
CONSTRAINT [ChannelAddress_PK] PRIMARY KEY CLUSTERED 
(
	[ChannelAddress_Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Channel](
	[Channel_Id] [int] IDENTITY(1,1) NOT NULL, 
	[ChannelAddress_Id] [int] NOT NULL,
	[ChannelType_Id] [int] NOT NULL,
	[Name] [nvarchar](12) NOT NULL,
	[Description] [nvarchar](80) NULL,
	[Used] [bit] NOT NULL,
	[Interval] [float] NULL,
	[OrdNr] [int] NULL,
	[Transtype] [int] NULL,
	[Factor] [float] NULL,
	[Offset] [float] NULL,
	[Operation] [int] NULL,
	[SaveStat] [bit] NOT NULL,
	[Sign] [bit] NULL,
	[Interval1] [float] NULL
CONSTRAINT [Channel_PK] PRIMARY KEY CLUSTERED 
(
	[Channel_Id] ASC
) ON [PRIMARY],
CONSTRAINT [Channel_Name_U] UNIQUE NONCLUSTERED 
(
	[Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Channel] ADD CONSTRAINT [ChannelAddress_Channel_FK1] FOREIGN KEY([ChannelAddress_Id])
REFERENCES [dbo].[ChannelAddress] ([ChannelAddress_Id])
GO

ALTER TABLE [dbo].[Channel] ADD CONSTRAINT [ChannelType_Channel_FK1] FOREIGN KEY([ChannelType_Id])
REFERENCES [dbo].[ChannelType] ([ChannelType_Id])
GO


CREATE TABLE [dbo].[ModuleType](
	[ModuleType_Id] [int] NOT NULL,
	[Name] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](80) NULL,
CONSTRAINT [ModuleType_PK] PRIMARY KEY CLUSTERED 
(
	[ModuleType_Id] ASC
) ON [PRIMARY],
CONSTRAINT [ModuleType_Name_U] UNIQUE NONCLUSTERED 
(
	[Name]
) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Module](
	[Module_Id] [int] IDENTITY(1,1) NOT NULL,
	[ModuleType_Id] [int] NOT NULL,
	[Name] [nvarchar](12) NOT NULL,
	[Description] [nvarchar](80) NULL,
	[Used] [bit] NOT NULL,
	[Config] [int] NULL
CONSTRAINT [Module_PK] PRIMARY KEY CLUSTERED 
(
	[Module_Id] ASC
),
CONSTRAINT [Module_Name_U] UNIQUE NONCLUSTERED 
(
	[Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Module] ADD CONSTRAINT [ModuleType_Module_FK1] FOREIGN KEY([ModuleType_Id])
REFERENCES [dbo].[ModuleType] ([ModuleType_Id])
GO


CREATE TABLE [dbo].[ModuleType_ChannelType](
	[ModuleType_ChannelType_Id] [int] IDENTITY(1,1) NOT NULL,
	[ModuleType_Id] [int] NOT NULL,
	[ChannelType_Id] [int] NOT NULL,
	[Nr] [int] NOT NULL,
	[Name] [nvarchar](12) NOT NULL,
 CONSTRAINT [ModuleType_ChannelType_PK] PRIMARY KEY CLUSTERED 
(
	[ModuleType_ChannelType_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ModuleType_ChannelType]  WITH CHECK ADD  CONSTRAINT [ChannelType_ModuleType_ChannelType_FK1] FOREIGN KEY([ChannelType_Id])
REFERENCES [dbo].[ChannelType] ([ChannelType_Id])
GO

ALTER TABLE [dbo].[ModuleType_ChannelType] CHECK CONSTRAINT [ChannelType_ModuleType_ChannelType_FK1]
GO

ALTER TABLE [dbo].[ModuleType_ChannelType]  WITH CHECK ADD  CONSTRAINT [ModuleType_ModuleType_ChannelType_FK1] FOREIGN KEY([ModuleType_Id])
REFERENCES [dbo].[ModuleType] ([ModuleType_Id])
GO

ALTER TABLE [dbo].[ModuleType_ChannelType] CHECK CONSTRAINT [ModuleType_ModuleType_ChannelType_FK1]
GO



CREATE TABLE [dbo].[Module_Channel](
	[Module_Id] [int] NOT NULL,
	[Channel_Id] [int] NOT NULL,
	[Nr] [int] NOT NULL
CONSTRAINT [Module_Channel_PK] PRIMARY KEY CLUSTERED 
(
	[Module_id],
	[Channel_Id],
	[Nr]
)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Module_Channel] ADD CONSTRAINT [Module_Module_Channel_FK1] FOREIGN KEY([Module_Id])
REFERENCES [dbo].[Module] ([Module_Id])
GO

ALTER TABLE [dbo].[Module_Channel] ADD CONSTRAINT [Channel_Module_Channel_FK1] FOREIGN KEY([Channel_Id])
REFERENCES [dbo].[Channel] ([Channel_Id])
GO


CREATE TABLE [dbo].[ModuleTypeParameter](
	[ModuleTypeParameter_Id] [int] IDENTITY(1,1) NOT NULL,	
	[ModuleType_Id] [int] NOT NULL,
	[Name] [nvarchar](12) NOT NULL,
	[Nr] [int] NOT NULL,
	[Value] [float] NULL,
	[StringValue] [nvarchar](255) NULL,
	[Unit] [nvarchar](8) NULL
CONSTRAINT [ModuleTypeParameters_PK] PRIMARY KEY CLUSTERED 
(
	[ModuleTypeParameter_Id]
)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ModuleTypeParameter] ADD CONSTRAINT [ModuleType_ModuleTypeParameter_FK1] FOREIGN KEY([ModuleType_Id])
REFERENCES [dbo].[ModuleType] ([ModuleType_Id])
GO


CREATE TABLE [dbo].[ModuleParameter](
	[ModuleParameter_Id] [int] IDENTITY(1,1) NOT NULL,
	[Module_Id] [int] NOT NULL,
	[Name] [nvarchar](12) NOT NULL,
	[Nr] [int] NOT NULL,
	[Value] [float] NULL,
	[StringValue] [nvarchar](255) NULL,
	[Unit] [nvarchar](8) NULL
CONSTRAINT [Module_Parameter_PK] PRIMARY KEY CLUSTERED 
(
	[ModuleParameter_Id]
)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ModuleParameter] ADD CONSTRAINT [Module_ModuleParameter_FK1] FOREIGN KEY([Module_Id])
REFERENCES [dbo].[Module] ([Module_Id])
GO






